﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.IO;

using Microsoft.AnalysisServices.AdomdClient;

using Ko2.DB.BI;

namespace Ko2.DB.BI.Reports
{
    public class Cash : MXBase
    {
        public enum Cube {Продажи =1, KPI = 2};

        public Exception Get(ref DataTable tbl, Cash.Cube cube = Cube.KPI)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return Get(ref tbl, ConnStr, Year, Month, Day, cube);
        }
        public Exception Get(ref DataTable tbl, DateTime parDT, Cash.Cube cube = Cube.KPI)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = parDT;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return Get(ref tbl, ConnStr, Year, Month, Day, cube);
        }
        public Exception Get(ref DataTable tbl, int parYear, int parMonth, int Day, Cash.Cube cube = Cube.KPI)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            return Get(ref tbl, ConnStr, parYear, parMonth, Day, cube);
        }
        public Exception Get(ref DataTable tbl, string parConnStr, int parYear, int parMonth, int Day, Cash.Cube cube = Cube.KPI)
        {

            #region Members

            Exception ret = null;
            DateTime dt = new DateTime(parYear, parMonth, Day);

            string cmdStr = "";

            #region SQL

            #region Cube Продажи

            if (cube == Cube.Продажи)
            {
                /*
    SELECT NON EMPTY 
    Hierarchize(AddCalculatedMembers({DrilldownLevel({
    [Дата].[День].[Все]})})) 
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
    ,[Дата].[День].[День].[Дата],[Дата].[День].[День].[День недели],[Дата].[День].[День].[Месяцы],[Дата].[День].[День].[Недели] 
    ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({
    DrilldownLevel({[Подразделение].[Подразделение].[Все]})})) 
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
    ,[Подразделение].[Подразделение].[Подразделение].[Регионы],[Подразделение].[Подразделение].[Подразделение].[Тип] ON ROWS  
    FROM 
    (SELECT ({[Номенклатура].[Иерархия номенклатуры].[Секция].&[ПОДГУЗНИКИ],[Номенклатура].[Иерархия номенклатуры].[Секция].&[ОДЕЖДА],[Номенклатура].[Иерархия номенклатуры].[Секция].&[ОБУВЬ],[Номенклатура].[Иерархия номенклатуры].[Секция].&[МЕБЕЛЬ],[Номенклатура].[Иерархия номенклатуры].[Секция].&[КОСМЕТИКА],[Номенклатура].[Иерархия номенклатуры].[Секция].&[ИГРУШКИ],[Номенклатура].[Иерархия номенклатуры].[Секция].&[ДЕТСКОЕ ПИТАНИЕ]}) 
    ON COLUMNS  
    FROM [Продажи]) 
    WHERE ([Дата].[Г-М-Д].[Месяц].&[201112],[Тип операции].[Тип операции].[Все],[Measures].[Оборот руб]) 
    CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
                */
                cmdStr =
        @"
SELECT NON EMPTY 
Hierarchize(AddCalculatedMembers({DrilldownLevel({
[Дата].[День].[Все]})})) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[День].[День].[Дата],[Дата].[День].[День].[День недели],[Дата].[День].[День].[Месяцы],[Дата].[День].[День].[Недели] 
ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({
DrilldownLevel({[Подразделение].[Подразделение].[Все]})})) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Подразделение].[Подразделение].[Подразделение].[Регионы],[Подразделение].[Подразделение].[Подразделение].[Тип] ON ROWS  
FROM 
(SELECT ({[Номенклатура].[Иерархия номенклатуры].[Секция].&[ПОДГУЗНИКИ],[Номенклатура].[Иерархия номенклатуры].[Секция].&[ОДЕЖДА],[Номенклатура].[Иерархия номенклатуры].[Секция].&[ОБУВЬ],[Номенклатура].[Иерархия номенклатуры].[Секция].&[МЕБЕЛЬ],[Номенклатура].[Иерархия номенклатуры].[Секция].&[КОСМЕТИКА],[Номенклатура].[Иерархия номенклатуры].[Секция].&[ИГРУШКИ],[Номенклатура].[Иерархия номенклатуры].[Секция].&[ДЕТСКОЕ ПИТАНИЕ]}) 
ON COLUMNS  
FROM [Продажи]) 
WHERE ([Дата].[Г-М-Д].[Месяц].&[" + MakeDT_Y_M(dt.Year, dt.Month) + @"],[Тип операции].[Тип операции].[Все],[Measures].[Оборот руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
";
            }

            #endregion

            #region Cube KPI

            if (cube == Cube.KPI)
            {
                /*
SELECT NON EMPTY 
Hierarchize({
DrilldownLevel({[Дата].[День].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[День].[День].[Дата]
,[Дата].[День].[День].[День недели]
,[Дата].[День].[День].[Месяцы]
,[Дата].[День].[День].[Недели] 
ON COLUMNS, NON EMPTY 
Hierarchize({DrilldownLevel({
[Подразделение].[Подразделение].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Подразделение].[Подразделение].[Подразделение].[Регионы]
,[Подразделение].[Подразделение].[Подразделение].[Тип] 
ON ROWS  
FROM [KPI] 
WHERE (
[Дата].[Г-М-Д].[Месяц].&
[201112]
,[Measures].[Оборот Все Руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
                */
                cmdStr =
        @"

SELECT NON EMPTY 
Hierarchize({
DrilldownLevel({[Дата].[День].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[День].[День].[Дата]
,[Дата].[День].[День].[День недели]
,[Дата].[День].[День].[Месяцы]
,[Дата].[День].[День].[Недели] 
ON COLUMNS, NON EMPTY 
Hierarchize({DrilldownLevel({
[Подразделение].[Подразделение].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Подразделение].[Подразделение].[Подразделение].[Регионы]
,[Подразделение].[Подразделение].[Подразделение].[Тип] 
ON ROWS  
FROM [KPI] 
WHERE (
[Дата].[Г-М-Д].[Месяц].&
[" + MakeDT_Y_M(dt.Year, dt.Month) + @"]
,[Measures].[Оборот Все Руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
";
            }

            #endregion

            #endregion SQL

            #endregion Members

            CellSet cst = null;
            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
                ret = new Exception(err.Message);
            else
            {
                err = Parse_001(ref cst, ref tbl);
                if (err != null)
                    ret = new Exception(err.Message);
            }

            return ret;
        }

        public Exception GetCubeCheck(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return GetCubeCheck(ref tbl, ConnStr, Year, Month, Day);
        }
        public Exception GetCubeCheck(ref DataTable tbl, DateTime parDT)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = parDT;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return GetCubeCheck(ref tbl, ConnStr, Year, Month, Day);
        }
        public Exception GetCubeCheck(ref DataTable tbl, int parYear, int parMonth, int Day)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            return GetCubeCheck(ref tbl, ConnStr, parYear, parMonth, Day);
        }
        public Exception GetCubeCheck(ref DataTable tbl, string parConnStr, int parYear, int parMonth, int Day)
        {

            #region Members

            Exception ret = null;
            DateTime dt = new DateTime(parYear, parMonth, Day);

            #region SQL
            /*
SELECT
{[Measures].[Кол-во чеков],[Measures].[Оборот руб],[Measures].[Кол-во магазинов в ДП]} 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , 
NON EMPTY Hierarchize(DrilldownMember({{DrilldownMember({{
DrilldownLevel({[Дата].[Г-М-Д].[Все]},,,INCLUDE_CALC_MEMBERS)}}
, {[Дата].[Г-М-Д].[Год].&
[2011]
},,,INCLUDE_CALC_MEMBERS)}}, 
{[Дата].[Г-М-Д].[Месяц].&
[201112]
,[Дата].[Г-М-Д].[Месяц].&
[201111]
,[Дата].[Г-М-Д].[Месяц].&
[201110]
,[Дата].[Г-М-Д].[Месяц].&
[201109]
},,,INCLUDE_CALC_MEMBERS)) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[Г-М-Д].[Месяц].[Кварталы],[Дата].[Г-М-Д].[Месяц].[Месяц],[Дата].[Г-М-Д].[Дата].[Дата],[Дата].[Г-М-Д].[Дата].[День недели],[Дата].[Г-М-Д].[Дата].[Месяцы],[Дата].[Г-М-Д].[Дата].[Недели] 
ON ROWS  FROM (
SELECT ({[Дата].[Г-М-Д].[Месяц].&
[201112]
}) ON COLUMNS  FROM [Чеки]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS         
         */
            string cmdStr =
    @"
SELECT
{[Measures].[Кол-во чеков],[Measures].[Оборот руб],[Measures].[Кол-во магазинов в ДП]} 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , 
NON EMPTY Hierarchize(DrilldownMember({{DrilldownMember({{
DrilldownLevel({[Дата].[Г-М-Д].[Все]},,,INCLUDE_CALC_MEMBERS)}}
, {[Дата].[Г-М-Д].[Год].&
[" + string.Format("{0:0000}", parYear) + @"]
},,,INCLUDE_CALC_MEMBERS)}}, 
{[Дата].[Г-М-Д].[Месяц].&
[" + MakeDT_Y_M(parYear, parMonth) + @"]
,[Дата].[Г-М-Д].[Месяц].&
[" + GetPrevMonth(dt, 1) + @"]
,[Дата].[Г-М-Д].[Месяц].&
[" + GetPrevMonth(dt, 2) + @"]
,[Дата].[Г-М-Д].[Месяц].&
[" + GetPrevMonth(dt, 3) + @"]
},,,INCLUDE_CALC_MEMBERS)) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[Г-М-Д].[Месяц].[Кварталы],[Дата].[Г-М-Д].[Месяц].[Месяц],[Дата].[Г-М-Д].[Дата].[Дата],[Дата].[Г-М-Д].[Дата].[День недели],[Дата].[Г-М-Д].[Дата].[Месяцы],[Дата].[Г-М-Д].[Дата].[Недели] 
ON ROWS  FROM (
SELECT ({[Дата].[Г-М-Д].[Месяц].&
[" + MakeDT_Y_M(parYear, parMonth) + @"]
}) ON COLUMNS  FROM [Чеки]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS         
";

            #endregion SQL

            #endregion Members

            CellSet cst = null;
            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
                ret = new Exception(err.Message);
            else
            {
                err = Parse_CubeCheck(ref cst, ref tbl);
                if (err != null)
                    ret = new Exception(err.Message);
                else
                {
                    if (tbl.Rows.Count > 3)
                    {
                        tbl.Rows.RemoveAt(1);
                        tbl.Rows.RemoveAt(1);
                    }
                }
            }

            return ret;
        }

        public Exception GetCubeCheckByDay(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return GetCubeCheckByDay(ref tbl, ConnStr, Year, Month, Day);
        }
        public Exception GetCubeCheckByDay(ref DataTable tbl, DateTime parDT)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = parDT;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return GetCubeCheckByDay(ref tbl, ConnStr, Year, Month, Day);
        }
        public Exception GetCubeCheckByDay(ref DataTable tbl, int parYear, int parMonth, int Day)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            return GetCubeCheckByDay(ref tbl, ConnStr, parYear, parMonth, Day);
        }
        public Exception GetCubeCheckByDay(ref DataTable tbl, string parConnStr, int parYear, int parMonth, int Day)
        {

            #region Members

            Exception ret = null;
            DateTime dt = new DateTime(parYear, parMonth, Day);

            #region SQL
/*
SELECT 
NON EMPTY Hierarchize({
DrilldownLevel({[Подразделение].[Подразделение].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Подразделение].[Подразделение].[Подразделение].[Регионы]
,[Подразделение].[Подразделение].[Подразделение].[Тип] 
ON COLUMNS  FROM [Чеки] 
WHERE 
([Подразделение].[Иерархия].[Все]
,[Дата].[Г-М-Д].[Дата].[01.07.2012]
,[Measures].[Кол-во чеков по оплате]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
*/
            string cmdStr =
@"
SELECT 
NON EMPTY Hierarchize({
DrilldownLevel({[Подразделение].[Подразделение].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Подразделение].[Подразделение].[Подразделение].[Регионы]
,[Подразделение].[Подразделение].[Подразделение].[Тип] 
ON COLUMNS  FROM [Чеки] 
WHERE 
([Подразделение].[Иерархия].[Все]
,[Дата].[Г-М-Д].[Дата].["
+ MakeDT_D_M_Y(dt.Day, dt.Month, dt.Year) + @"]
,[Measures].[Кол-во чеков по оплате]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
";

            #endregion SQL

            #endregion Members

            CellSet cst = null;
            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
                ret = new Exception(err.Message);
            else
            {
                err = Parse_CubeCheckByDay(ref cst, ref tbl);
                if (err != null)
                    ret = new Exception(err.Message);
                else
                {
                    if (tbl.Rows.Count > 3)
                    {
                        tbl.Rows.RemoveAt(1);
                        tbl.Rows.RemoveAt(1);
                    }
                }
            }

            return ret;
        }

        public Exception GetCubeTrade(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return GetCubeTrade(ref tbl, ConnStr, Year, Month, Day);
        }
        public Exception GetCubeTrade(ref DataTable tbl, DateTime parDT)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = parDT;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return GetCubeTrade(ref tbl, ConnStr, Year, Month, Day);
        }
        public Exception GetCubeTrade(ref DataTable tbl, int parYear, int parMonth, int Day)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            return GetCubeTrade(ref tbl, ConnStr, parYear, parMonth, Day);
        }
        public Exception GetCubeTrade(ref DataTable tbl, string parConnStr, int parYear, int parMonth, int Day)
        {

            #region Members

            Exception ret = null;
            DateTime dt = new DateTime(parYear, parMonth, Day);

            #region SQL
            /*
SELECT
{[Measures].[Кол-во чеков],[Measures].[Оборот руб],[Measures].[Кол-во магазинов в ДП]} 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , 
NON EMPTY Hierarchize(DrilldownMember({{DrilldownMember({{
DrilldownLevel({[Дата].[Г-М-Д].[Все]},,,INCLUDE_CALC_MEMBERS)}}
, {[Дата].[Г-М-Д].[Год].&
[2011]
},,,INCLUDE_CALC_MEMBERS)}}, 
{[Дата].[Г-М-Д].[Месяц].&
[201112]
,[Дата].[Г-М-Д].[Месяц].&
[201111]
,[Дата].[Г-М-Д].[Месяц].&
[201110]
,[Дата].[Г-М-Д].[Месяц].&
[201109]
},,,INCLUDE_CALC_MEMBERS)) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[Г-М-Д].[Месяц].[Кварталы],[Дата].[Г-М-Д].[Месяц].[Месяц],[Дата].[Г-М-Д].[Дата].[Дата],[Дата].[Г-М-Д].[Дата].[День недели],[Дата].[Г-М-Д].[Дата].[Месяцы],[Дата].[Г-М-Д].[Дата].[Недели] 
ON ROWS  FROM (
SELECT ({[Дата].[Г-М-Д].[Месяц].&
[201112]
}) ON COLUMNS  FROM [Чеки]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS         
         */
            string cmdStr =
    @"
SELECT {[Measures].[Оборот руб],[Measures].[Кол-во магазинов в ДП]} 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS 
, NON EMPTY Hierarchize(DrilldownMember({{DrilldownMember({{DrilldownLevel({
[Дата].[Г-М-Д].[Все]},,,INCLUDE_CALC_MEMBERS)}}, {[Дата].[Г-М-Д].[Год].&
[" + string.Format("{0:0000}", parYear) + @"]
}
,,,INCLUDE_CALC_MEMBERS)}}, 
{[Дата].[Г-М-Д].[Месяц].&
[" + MakeDT_Y_M(parYear, parMonth) + @"]
,[Дата].[Г-М-Д].[Месяц].&
[" + GetPrevMonth(dt, 1) + @"],[Дата].[Г-М-Д].[Месяц].&
[" + GetPrevMonth(dt, 2) + @"],[Дата].[Г-М-Д].[Месяц].&
[" + GetPrevMonth(dt, 3) + @"]},,,INCLUDE_CALC_MEMBERS)) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[Г-М-Д].[Месяц].[Кварталы],[Дата].[Г-М-Д].[Месяц].[Месяц],[Дата].[Г-М-Д].[Дата].[Дата],[Дата].[Г-М-Д].[Дата].[День недели],[Дата].[Г-М-Д].[Дата].[Месяцы],[Дата].[Г-М-Д].[Дата].[Недели] ON ROWS  
FROM (SELECT ({[Дата].[Г-М-Д].[Месяц].&
[" + MakeDT_Y_M(parYear, parMonth) + @"]
}) 
ON COLUMNS  
FROM [Продажи]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS 
";

            #endregion SQL

            #endregion Members

            CellSet cst = null;
            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
                ret = new Exception(err.Message);
            else
            {
                err = Parse_CubeTrade(ref cst, ref tbl);
                if (err != null)
                    ret = new Exception(err.Message);
                else
                {
                    if (tbl.Rows.Count > 3)
                    {
                        tbl.Rows.RemoveAt(1);
                        tbl.Rows.RemoveAt(1);
                    }
                }
            }

            return ret;
        }

        public Exception Parse_001(ref CellSet parCst, ref DataTable parTbl)
        {

            Exception ret = null;

            #region Init

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #endregion

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            MakeTbl_001(ref parCst, ref parTbl, col_count);

            int count = 0;
            DataRow r = null;

            #endregion

            #region for

            for (cur_row = 0; cur_row < row_count - 1; cur_row++)
            {

                count++;
                r = parTbl.NewRow();

                #region Data

                for (int i = 0; i < col_count - 1; i++)
                {
                    if (parCst[i, cur_row].Value != null)
                        r[i + 1] = Convert.ToDecimal(parCst[i, cur_row].Value);
                    else
                        r[i + 1] = DBNull.Value;
                }

                r[0] = parCst.Axes[1].Positions[cur_row].Members[0].Caption;

                parTbl.Rows.Add(r);

                #endregion

            }

            #endregion

            return ret;
        }
        public Exception Parse_CubeCheck(ref CellSet parCst, ref DataTable parTbl)
        {

            Exception ret = null;

            #region Init

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #endregion

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            MakeTbl_CubeCash(ref parCst, ref parTbl, col_count);

            int count = 0;
            DataRow r = null;

            #endregion

            #region for

            for (cur_row = 0; cur_row < row_count - 1; cur_row++)
            {

                count++;
                r = parTbl.NewRow();

                #region Data

                for (int i = 0; i < col_count - 1; i++)
                {
                    if (parCst[i, cur_row].Value != null)
                        r[i + 1] = Convert.ToDecimal(parCst[i, cur_row].Value);
                    else
                        r[i + 1] = DBNull.Value;
                }

                r[0] = parCst.Axes[1].Positions[cur_row].Members[0].Caption;

                parTbl.Rows.Add(r);

                #endregion

            }

            #endregion

            return ret;
        }
        public Exception Parse_CubeCheckByDay(ref CellSet parCst, ref DataTable parTbl)
        {

            Exception ret = null;

            #region Init

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 1)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if ( !(parCst.Axes[0].Positions.Count > 0) )
                return new Exception("No data returned for the selection");

            #endregion

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[0].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Total rows and columns
            //row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            row_count = 1;
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            MakeTbl_CubeCashByDay(ref parCst, ref parTbl, col_count);

            int count = 0;
            DataRow r = null;

            #endregion

            #region for

            for (cur_row = 0; cur_row < row_count; cur_row++)
            {

                count++;
                r = parTbl.NewRow();

                #region Data

                for (int i = 0; i < col_count - 1; i++)
                {
                    if (parCst[i].Value != null)
                        r[i] = Convert.ToDecimal(parCst[i].Value);
                    else
                        r[i] = DBNull.Value;
                }

                //r[0] = parCst.Axes[1].Positions[cur_row].Members[0].Caption;

                parTbl.Rows.Add(r);

                #endregion

            }

            #endregion

            return ret;
        }
        public Exception Parse_CubeTrade(ref CellSet parCst, ref DataTable parTbl)
        {

            Exception ret = null;

            #region Init

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #endregion

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            MakeTbl_CubeTrade(ref parCst, ref parTbl, col_count);

            int count = 0;
            DataRow r = null;

            #endregion

            #region for

            for (cur_row = 0; cur_row < row_count - 1; cur_row++)
            {

                count++;
                r = parTbl.NewRow();

                #region Data

                for (int i = 0; i < col_count - 1; i++)
                {
                    if (parCst[i, cur_row].Value != null)
                        r[i + 1] = Convert.ToDecimal(parCst[i, cur_row].Value);
                    else
                        r[i + 1] = DBNull.Value;
                }

                r[0] = parCst.Axes[1].Positions[cur_row].Members[0].Caption;

                parTbl.Rows.Add(r);

                #endregion

            }

            #endregion

            return ret;
        }

        public void MakeTbl_001(ref CellSet parCst, ref DataTable parTbl, int cnt)
        {
            int row_dim_count = 0, col_count;
            parTbl = new DataTable("Tbl");
            parTbl.Columns.Add("Магазин");

            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            col_count = parCst.Axes[0].Positions.Count + row_dim_count;

            for (int i = 0; i < col_count - 1; i++)
            {
                string ColNameStr = parCst.Axes[0].Positions[i].Members[0].Caption;
                parTbl.Columns.Add(ColNameStr, typeof(Decimal));
            }

        }
        public void MakeTbl_CubeCash(ref CellSet parCst, ref DataTable parTbl, int cnt)
        {
            int row_dim_count = 0, col_count;
            parTbl = new DataTable("Tbl");
            parTbl.Columns.Add("Дата");

            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            col_count = parCst.Axes[0].Positions.Count + row_dim_count;

            for (int i = 0; i < col_count - 1; i++)
            {
                string ColNameStr = parCst.Axes[0].Positions[i].Members[0].Caption;
                parTbl.Columns.Add(ColNameStr, typeof(Decimal));
            }

        }
        public void MakeTbl_CubeCashByDay(ref CellSet parCst, ref DataTable parTbl, int cnt)
        {
            int row_dim_count = 0, col_count;
            parTbl = new DataTable("Tbl");

            //if (parCst.Axes[1].Positions[0].Members.Count > 0)
            //    row_dim_count = parCst.Axes[1].Positions[0].Members.Count;
            row_dim_count = 1;
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;

            for (int i = 0; i < col_count - 1; i++)
            {
                string ColNameStr = parCst.Axes[0].Positions[i].Members[0].Caption;
                parTbl.Columns.Add(ColNameStr, typeof(Decimal));
            }

        }
        public void MakeTbl_CubeTrade(ref CellSet parCst, ref DataTable parTbl, int cnt)
        {
            int row_dim_count = 0, col_count;
            parTbl = new DataTable("Tbl");
            parTbl.Columns.Add("Дата");

            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            col_count = parCst.Axes[0].Positions.Count + row_dim_count;

            for (int i = 0; i < col_count - 1; i++)
            {
                string ColNameStr = parCst.Axes[0].Positions[i].Members[0].Caption;
                parTbl.Columns.Add(ColNameStr, typeof(Decimal));
            }

        }

    }

}
